﻿using QWManage.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace QWManage.DAL
{
    public class SqlDAL
    {
        protected string TableName { set; get; }
        public static object SqlNull(object obj)
        {
            if (obj == null)
                return DBNull.Value;

            return obj;
        }
        public static object SqlIntNull(int? obj)
        {
            if (obj == null && !obj.HasValue)
                return DBNull.Value;

            return obj.Value;
        }
        public void ClearDataBase()
        {
            List<string> names = new List<string>();
            using (SqlConnection connection = new SqlConnection(SQLCON))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand("SELECT name FROM sys.tables", connection))
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        names.Add((string)reader["name"]);
                    }
                }
            }
            foreach (string name in names)
            {
                using (SqlConnection connection = new SqlConnection(SQLCON))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand($"truncate table {name}", connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        public static SqlParameter GetStringSqlParameter(string name, string value)
        {
            if (!name.StartsWith("@")) name = "@" + name;
            if (string.IsNullOrEmpty(value))
                return new SqlParameter(name, DBNull.Value);
            else
                return new SqlParameter(name, value);
        }

        public static SqlParameter GetIntSqlParameter(string name, int? value)
        {
            if (!name.StartsWith("@")) name = "@" + name;
            if (!value.HasValue)
                return new SqlParameter(name, DBNull.Value);
            else
                return new SqlParameter(name, value);
        }

        public static int ReadInt(SqlDataReader reader, string name)
        {
            return (int)reader[name];
        }
        public static decimal? ReadDecimalNull(SqlDataReader reader, string name)
        {
            if (reader[name] == DBNull.Value) return null;
            return (decimal)reader[name];
        }

        public static int? ReadIntNull(SqlDataReader reader, string name)
        {
            if (reader[name] == DBNull.Value) return null;
            return (int)reader[name];
        }
        
        public static bool ReadBool(SqlDataReader reader, string name)
        {
            return (bool)reader[name];
        }

        public static string ReadString(SqlDataReader reader, string name)
        {
            if (reader[name] == DBNull.Value) return null;
            else return ((string)reader[name]).Trim();
        }

        public static DateTime? ReadDateTimeNull(SqlDataReader reader, string name)
        {
            if (reader[name] == DBNull.Value) return null;
            else return (DateTime)reader[name];
        }


        public string SQLCON;
        public SqlDAL()
        {
            AppConfigInfo appConfigInfo = AppCofingMgr.Get();
            SQLCON = appConfigInfo.SqlConnStr;
        }

        public static bool ConnectionTest(
            string dataSource,
            string initialCatalog,
            string userID,
            string password, ref string message)
        {
            string connstr = $"" +
                   $"Data Source={dataSource};" +
                   $"Initial Catalog={initialCatalog};" +
                   $"User ID={userID};" +
                   $"Password={password};";
            try
            {
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    conn.Close();
                    return true;
                }
            }
            catch (Exception ex)
            {
                message = ex.Message;
                return false;
            }
        }
    }
}
